package cn.exam.sys.dao.impl;

import cn.exam.common.DBUnitHelper;
import cn.exam.common.PageControl;
import cn.exam.common.Pager;
import cn.exam.model.Score;
import cn.exam.model.Subject;
import cn.exam.sys.dao.interfaces.IScoreDao;

public class ScoreDao implements IScoreDao {

	@Override
	public Pager<Score> list(PageControl pc) {
		StringBuilder sqlBuilder = new StringBuilder();
		sqlBuilder.append("SELECT (select u.USERTRUENAME from sysuser u where u.USERID=s.USERID limit 1) studentName,")
		.append("( SELECT p.pname FROM paper p WHERE p.pno = s.pno LIMIT 1 ) pname,count(IF(studentstate = 1, TRUE, NULL ))*2 AS score,spid ")
		.append(" FROM studentpaper s GROUP BY spid,userid,s.pno order by s.spid desc ");
		
		String spid="spid";
		Pager<Score> pager;
		pager = DBUnitHelper.execlist(sqlBuilder.toString(), pc, Score.class, spid);	
		return pager;
	}

    @Override
    public void deleteScore(String spid) {
        String sql = "DELETE FROM studentpaper WHERE spid = ?";
        DBUnitHelper.executeUpdate(sql, spid);
    }
}
